package nc.bs.cm.report.impl;

import nc.bs.cm.report.itf.IBudgetExecutionByMonth;
import nc.bs.dao.BaseDAO;
import nc.bs.logging.Logger;
import nc.jdbc.framework.processor.MapListProcessor;
import nc.pub.hrpbm.util.HrpbmReportUtils;
import nc.pub.smart.context.SmartContext;
import nc.pub.smart.data.DataSet;
import nc.pub.smart.metadata.Field;
import nc.pub.smart.metadata.MetaData;
import nc.vo.pub.BusinessException;
import nc.vo.pubapp.pattern.exception.ExceptionUtils;
import nccloud.commons.lang.ArrayUtils;
import nccloud.commons.lang.StringUtils;

import java.math.BigDecimal;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 院预算执行表（按月）
 * ouyanggx
 * 2025/11/12
 * */
public class BudgetExecutionByMonthImpl implements IBudgetExecutionByMonth {
    String[] fields = new String[]{
            "budgettypename", "nyear", "deadline", "code", "ywcode",
            "pk_inoutbusiclass", "szxmcode", "szxmname", "ejxmname", "zjlyname",
            "sfzjcode", "sfzjname", "kmlxcode", "kmlxname",
            "kjkmcode", "kjkmname", "srxmcode", "srxmname",
            "firstdown", "adjust", "outAmount", "inAmount", "lastamount", "localdebitamount", "zxl", "jflx"};

    String[] numberCloumns = new String[]{
            "firstdown", "adjust", "outAmount", "inAmount", "lastamount", "localdebitamount", "zxl"
    };

    String[] fieldCaptions = new String[]{
            "交易类型", "预算年度", "截止月份", "凭证收支项目", "业务收支项目",
            "收支项目主键", "收支项目编码", "收支项目名称", "二级项目", "资金来源",
            "是否资金编码", "是否资金", "科目类型编码", "科目类型名称",
            "会计科目编码", "会计科目名称", "对照收支项目编码", "对照收支项目名称",
            "批复金额", "调剂金额", "跨科室调出金额", "跨科室调入金额", "最终预算数", "执行金额", "执行率", "经费类型"};

    private BaseDAO baseDAO;

    public BaseDAO getBaseDAO() {
        return baseDAO == null ? new BaseDAO() : baseDAO;
    }

    @Override
    public DataSet process(SmartContext context) throws BusinessException {
        MetaData metadata = this.initMetaData();
        HrpbmReportUtils.handelContext(context);
        List<Map<String, Object>> sqlResult = getBusinessData(context);

        return buildDataSetobj(metadata, sqlResult, fields);
    }

    @SuppressWarnings("unchecked")
    public List<Map<String, Object>> getBusinessData(SmartContext context) throws BusinessException {

        String year = (String) context.getAttribute("year");
        String kmlx = (String) context.getAttribute("kmlx");
        // 截止月份(03=1-3月；06=1-6月；09=1-9月；12=1-12月)
        String deadline = (String) context.getAttribute("deadline");
        if (StringUtils.isEmpty(deadline)) {
            return new ArrayList<>();
        }
        if (deadline.length() == 1) {
            deadline = "0" + deadline;
        }
        String[] deadlineEnum = new String[]{"01","02","03","04","05","06","07","08","09","10","11","12"};

        // 不直接抛错，因为会影响语义模型的向导
        if (StringUtils.isEmpty(year) || StringUtils.isEmpty(kmlx) || !Arrays.asList(deadlineEnum).contains(deadline)) {
            Logger.error("查询条件错误");
            return new ArrayList<>();
        }

        StringBuilder sql = new StringBuilder();

        // 外层查询字段定义
        sql.append("SELECT ");
        sql.append("budgettype budgettypename, ");
        sql.append("pk_budgettype pk_budgettype, ");
        sql.append("nyear nyear, ");
        sql.append("'").append(deadline).append("' deadline, ");
        sql.append("sfsjzj sfzjname, ");
        sql.append("kmlb kmlxname, ");
        sql.append("pk_inoutbusiclass pk_inoutbusiclass, ");
        sql.append("szxm_code szxmcode, ");
        sql.append("szxm_name szxmname, ");
        sql.append("fundsourcename zjlyname, ");
        sql.append("sum(pfje) firstdown, ");
        sql.append("sum(swap_amount_out) outAmount, ");
        sql.append("sum(swap_amount_in) inAmount ");

        // 子查询开始
        sql.append("FROM ( ");
        sql.append("SELECT ");
        sql.append("hrpbm_budgetmake.nyear, ");
        sql.append("hrpbm_budgettype.name budgettype, ");
        sql.append("hrpbm_budgettype.pk_budgettype pk_budgettype, ");
        sql.append("bd_inoutbusiclass.pk_inoutbusiclass pk_inoutbusiclass, ");
        sql.append("bd_inoutbusiclass.code szxm_code, ");
        sql.append("bd_inoutbusiclass.name szxm_name, ");
        sql.append("bd_fundsource.name fundsourcename, ");
        // CASE 表达式
        sql.append("CASE ");
        sql.append("WHEN hrpbm_budgetmake.taskstage = 3 THEN hrpbm_budgetmakedetail.amount_seconddown ");
        sql.append("ELSE hrpbm_budgetmakedetail.amount_firstdown ");
        sql.append("END pfje, ");
        sql.append("docview.sfsjzj, ");
        sql.append("docview.kmlb, ");
        sql.append("budgetdept_out.swap_amount_out, ");
        sql.append("budgetdept_in.swap_amount_in ");

        // 主表关联
        sql.append("FROM hrpbm_budgetmake hrpbm_budgetmake ");
        // 关联预算类型表
        sql.append("JOIN hrpbm_budgettype hrpbm_budgettype ON ");
        sql.append("hrpbm_budgettype.pk_budgettype = hrpbm_budgetmake.pk_budgettype ");
        sql.append("AND hrpbm_budgettype.code IN ('YS01', 'YS02', 'YS03') ");
        // 关联预算明细
        sql.append("JOIN hrpbm_budgetmakedetail hrpbm_budgetmakedetail ON ");
        sql.append("hrpbm_budgetmakedetail.pk_budgetmake = hrpbm_budgetmake.pk_budgetmake ");
        sql.append("AND nvl(hrpbm_budgetmakedetail.dr, 0) = 0 ");
        // 关联收支项目分类
        sql.append("LEFT JOIN bd_inoutbusiclass bd_inoutbusiclass ON ");
        sql.append("bd_inoutbusiclass.pk_inoutbusiclass = hrpbm_budgetmakedetail.pk_inoutclass ");
        // 关联资金来源
        sql.append("LEFT JOIN bd_fundsource bd_fundsource ON ");
        sql.append("bd_fundsource.pk_fundsource = hrpbm_budgetmakedetail.pk_fundsource ");

        // 子查询：docview（是否资金、科目类型）
        sql.append("LEFT JOIN ( ");
        sql.append("SELECT ");
        sql.append("fip_docview_b.factorvalue5, ");
        sql.append("sfsjzj.name sfsjzj, ");
        sql.append("kmlb.name kmlb ");
        sql.append("FROM fip_docview fip_docview ");
        sql.append("JOIN fip_docview_b fip_docview_b ON ");
        sql.append("fip_docview_b.pk_classview = fip_docview.pk_classview ");
        sql.append("LEFT JOIN bd_defdoc sfsjzj ON sfsjzj.pk_defdoc = fip_docview_b.factorvalue3 ");
        sql.append("LEFT JOIN bd_defdoc kmlb ON kmlb.pk_defdoc = fip_docview_b.factorvalue6 ");
        sql.append("WHERE fip_docview.viewcode = 'YS001' ");
        sql.append("AND factorvalue6 = '").append(kmlx).append("' ");
        sql.append("GROUP BY fip_docview_b.factorvalue5, sfsjzj.name, kmlb.name ");
        sql.append(") docview ON docview.factorvalue5 = hrpbm_budgetmakedetail.pk_inoutclass ");

        // 子查询：跨科室调出
        sql.append("LEFT JOIN ( ");
        sql.append("SELECT ");
        sql.append("sum(hrpbm_budgetswap_b.swap_amount) swap_amount_out, ");
        sql.append("hrpbctp_busictrldata.dimno, ");
        sql.append("hrpbm_budgetswap_b.pk_budgetdept_out ");
        sql.append("FROM hrpbm_budgetswap hrpbm_budgetswap ");
        sql.append("JOIN hrpbm_budgetswap_b hrpbm_budgetswap_b ON ");
        sql.append("hrpbm_budgetswap_b.pk_budgetswap = hrpbm_budgetswap.PK_BUDGETSWAP ");
        sql.append("LEFT JOIN hrpbctp_busictrldata hrpbctp_busictrldata ON ");
        sql.append("hrpbctp_busictrldata.pk_busictrldata = hrpbm_budgetswap_b.budgetno_in ");
        sql.append("WHERE hrpbm_budgetswap.approvestatus = 1 ");
        sql.append("and substr(hrpbm_budgetswap.approvedate,0,4) ='").append(year).append("' ");
        sql.append("and substr(hrpbm_budgetswap.approvedate,6,2) <='").append(deadline).append("' ");
        sql.append("GROUP BY hrpbctp_busictrldata.dimno, hrpbm_budgetswap_b.pk_budgetdept_out ");
        sql.append(") budgetdept_out ON ");
        sql.append("budgetdept_out.dimno = hrpbm_budgetmakedetail.budgetno ");
        sql.append("AND budgetdept_out.pk_budgetdept_out = hrpbm_budgetmake.pk_budgetdept ");

        // 子查询：跨科室调入
        sql.append("LEFT JOIN ( ");
        sql.append("SELECT ");
        sql.append("sum(hrpbm_budgetswap_b.swap_amount) swap_amount_in, ");
        sql.append("hrpbctp_busictrldata.dimno, ");
        sql.append("hrpbm_budgetswap_b.pk_budgetdept_in ");
        sql.append("FROM hrpbm_budgetswap hrpbm_budgetswap ");
        sql.append("JOIN hrpbm_budgetswap_b hrpbm_budgetswap_b ON ");
        sql.append("hrpbm_budgetswap_b.pk_budgetswap = hrpbm_budgetswap.PK_BUDGETSWAP ");
        sql.append("LEFT JOIN hrpbctp_busictrldata hrpbctp_busictrldata ON ");
        sql.append("hrpbctp_busictrldata.pk_busictrldata = hrpbm_budgetswap_b.budgetno_in ");
        sql.append("WHERE hrpbm_budgetswap.approvestatus = 1 ");
        sql.append("and substr(hrpbm_budgetswap.approvedate,0,4) ='").append(year).append("' ");
        sql.append("and substr(hrpbm_budgetswap.approvedate,6,2) <='").append(deadline).append("' ");
        sql.append("GROUP BY hrpbctp_busictrldata.dimno, hrpbm_budgetswap_b.pk_budgetdept_in ");
        sql.append(") budgetdept_in ON ");
        sql.append("budgetdept_in.dimno = hrpbm_budgetmakedetail.budgetno ");
        sql.append("AND budgetdept_in.pk_budgetdept_in = hrpbm_budgetmake.pk_budgetdept ");

        // 主查询筛选条件
        sql.append("WHERE hrpbm_budgetmake.approvestatus = 1 ");
        sql.append("AND hrpbm_budgetmake.nyear = '").append(year).append("' ");
        sql.append("AND nvl(hrpbm_budgetmake.dr, 0) = 0 ");
        sql.append("AND ( ");
        sql.append("(hrpbm_budgettype.code = 'YS03' AND hrpbm_budgetmake.taskstage = 3) ");
        sql.append("OR (hrpbm_budgettype.code IN ('YS02') AND hrpbm_budgetmake.taskstage = 1 ");
        if ("2025".equals(year)) {
            sql.append("AND hrpbm_budgetmake.pk_budgetdept NOT IN ( ");
            sql.append("SELECT pk_budgetdept FROM hrpbm_budgetdept WHERE name = '设备物资部') ");
        }
        sql.append(") ");
        sql.append("OR (hrpbm_budgettype.code IN ('YS01') AND hrpbm_budgetmake.taskstage = 1) ");
        sql.append(") ");
        sql.append(") "); // 子查询结束

        // GROUP BY 子句
        sql.append("GROUP BY ");
        sql.append("budgettype, ");
        sql.append("pk_budgettype, ");
        sql.append("nyear, ");
        sql.append("sfsjzj, ");
        sql.append("kmlb, ");
        sql.append("pk_inoutbusiclass, ");
        sql.append("szxm_code, ");
        sql.append("szxm_name, ");
        sql.append("fundsourcename ");

        // ORDER BY 子句
        sql.append("ORDER BY ");
        sql.append("szxm_code, ");
        sql.append("fundsourcename");

        List<Map<String, Object>> mainData = (List<Map<String, Object>>)
                getBaseDAO().executeQuery(sql.toString(), new MapListProcessor());

        // 调整金额
        sql.setLength(0);
        sql.append("SELECT ")
                .append("hrpbm_budgetadjustdetail.pk_inoutclass,hrpbm_budgetadjust.pk_budgettype, ")
                .append("bd_fundsource.name, ")
                .append("SUM(amount_adjust) AS amount_adjust ");
        sql.append("FROM hrpbm_budgetadjustdetail hrpbm_budgetadjustdetail ");
        sql.append("JOIN hrpbm_budgetadjust hrpbm_budgetadjust ")
                .append("ON hrpbm_budgetadjust.pk_budgetadjust = hrpbm_budgetadjustdetail.pk_budgetadjust ")
                .append("AND NVL(hrpbm_budgetadjust.dr, 0) = 0 ")
                .append("and hrpbm_budgetadjust.approvestatus=1 ");
        sql.append("LEFT JOIN bd_fundsource bd_fundsource ON ");
        sql.append("bd_fundsource.pk_fundsource = hrpbm_budgetadjustdetail.pk_fundsource ");
        sql.append("WHERE 1 = 1 ")
                .append("AND NVL(hrpbm_budgetadjustdetail.dr, 0) = 0 ")
                .append("AND hrpbm_budgetadjustdetail.srcbillbid IS NOT NULL ")
                .append("AND SUBSTR(hrpbm_budgetadjust.approvedate, 0, 4) = '").append(year).append("' ")
                .append("AND SUBSTR(hrpbm_budgetadjust.approvedate, 6, 2) <= '")
                .append(deadline) // 月份补零（确保格式统一，如9→09）
                .append("' ");
        sql.append("GROUP BY ")
                .append("hrpbm_budgetadjustdetail.pk_inoutclass,hrpbm_budgetadjust.pk_budgettype, ")
                .append("bd_fundsource.name ");
        List<Map<String, Object>> adjustData = (List<Map<String, Object>>)
                getBaseDAO().executeQuery(sql.toString(), new MapListProcessor());
        Map<String, Object> adjust = new HashMap<>();
        if (adjustData != null && adjustData.size() > 0) {
            for (Map<String, Object> map : adjustData) {
                adjust.put((String) map.get("pk_inoutclass") + (String) map.get("pk_budgettype") +
                        (String) map.get("name"), map.get("amount_adjust"));
            }
        }

        // 执行金额
        sql.setLength(0);
        // 外层查询字段
        sql.append("SELECT ");
        sql.append("fip_docview_b.factorvalue5, ");
        sql.append("balance.jflx, ");
        sql.append("sum(balance.localdebitamount) localdebitamount ");

        // 外层 FROM 及表关联
        sql.append("FROM ");
        sql.append("fip_docview fip_docview ");
        sql.append("JOIN fip_docview_b fip_docview_b ON ");
        sql.append("fip_docview_b.pk_classview = fip_docview.pk_classview ");

        // 左关联 balance 子查询
        sql.append("LEFT JOIN ( ");
        // balance 子查询字段
        sql.append("SELECT ");
        sql.append("a.pk_accasoa, ");
        sql.append("bd_defdoc.name jflx, ");
        sql.append("NVL(SUM(g.debitamount), 0) localdebitamount ");

        // balance 子查询 FROM 及关联
        sql.append("FROM ");
        sql.append("bd_accasoa a ");
        sql.append("LEFT JOIN bd_account ac ON a.pk_account = ac.pk_account ");

        // 递归子查询
        sql.append("LEFT JOIN ( ");
        sql.append("SELECT ");
        sql.append("CONNECT_BY_ROOT pk_account AS root_pk_account, ");
        sql.append("pk_account AS child_pk_account ");
        sql.append("FROM ");
        sql.append("bd_account ");
        sql.append("CONNECT BY PRIOR pk_account = pid ");
        sql.append(") rec ON ac.pk_account = rec.root_pk_account ");

        // 关联子科目对应的 bd_accasoa 及后续表
        sql.append("LEFT JOIN bd_accasoa a_child ON a_child.pk_account = rec.child_pk_account ");
        sql.append("LEFT JOIN gl_detail g ON a_child.pk_accasoa = g.pk_accasoa ");
        sql.append("LEFT JOIN gl_docfree1 gl_docfree1 ON gl_docfree1.assid = g.assid ");
        sql.append("LEFT JOIN bd_defdoc bd_defdoc ON bd_defdoc.pk_defdoc = gl_docfree1.F18 ");
        sql.append("LEFT JOIN gl_voucher ON gl_voucher.pk_voucher = g.pk_voucher ");

        // balance 子查询 WHERE 条件
        sql.append("WHERE ");
        sql.append("g.pk_accountingbook = '1001A210000000000R84' ");
        sql.append("AND g.tempsaveflag <> 'Y' ");
        sql.append("AND g.discardflagv <> 'Y' ");
        sql.append("AND g.voucherkindv <> 255 ");
        sql.append("AND gl_voucher.period > '00' ");
        sql.append("AND gl_voucher.period <='").append(deadline).append("' ");
        sql.append("AND gl_voucher.year = '").append(year).append("' ");

        // balance 子查询 GROUP BY
        sql.append("GROUP BY ");
        sql.append("a.pk_accasoa, ");
        sql.append("bd_defdoc.name ");

        // 结束 balance 子查询，指定关联条件
        sql.append(") balance ON ");
        sql.append("balance.pk_accasoa = fip_docview_b.desdocvalue ");

        // 外层 WHERE 条件
        sql.append("WHERE ");
        sql.append("fip_docview.viewcode = 'YS001' ");
        sql.append("AND factorvalue6 = '").append(kmlx).append("' ");

        // 外层 GROUP BY
        sql.append("GROUP BY ");
        sql.append("fip_docview_b.factorvalue5, ");
        sql.append("balance.jflx");

        List<Map<String, Object>> debitamountData = (List<Map<String, Object>>)
                getBaseDAO().executeQuery(sql.toString(), new MapListProcessor());
        Map<String, Object> debitamount = new HashMap<>();
        if (debitamountData != null && debitamountData.size() > 0) {
            for (Map<String, Object> map : debitamountData) {
                debitamount.put((String) map.get("factorvalue5") + (String) map.get("jflx"), map.get("localdebitamount"));
            }
        }

        // 使用收支项目主键+资金来源名称作为报表的唯一值，关联调整金额和执行金额，防止出现笛卡尔积膨胀
        if (mainData != null && mainData.size() > 0) {
            for (Map<String, Object> map : mainData) {
                String pk_inoutbusiclass = (String) map.get("pk_inoutbusiclass");// 收支项目
                String zjlyname = (String) map.get("zjlyname");// 资金来源名称
                String pk_budgettype =  (String) map.get("pk_budgettype");// 预算类型
                if (adjust.containsKey(pk_inoutbusiclass + pk_budgettype + zjlyname)) {
                    BigDecimal adjust_amount = convertToBigDecimal(adjust.get(pk_inoutbusiclass + pk_budgettype + zjlyname));// 执行金额
                    map.put("adjust", adjust_amount);
                }

                // 计算 最终预算数=批复金额+调整金额-跨科室调出金额+跨科室调入金额
                BigDecimal firstdown = map.get("firstdown")==null ?
                        BigDecimal.ZERO : convertToBigDecimal(map.get("firstdown"));
                BigDecimal adjust_amount = map.get("adjust")==null ?
                        BigDecimal.ZERO : convertToBigDecimal(map.get("adjust"));
                BigDecimal outAmount = map.get("outAmount")==null ?
                        BigDecimal.ZERO : convertToBigDecimal(map.get("outAmount"));
                BigDecimal inAmount = map.get("inAmount")==null ?
                        BigDecimal.ZERO : convertToBigDecimal(map.get("inAmount"));
                BigDecimal lastamount = firstdown.add(adjust_amount).subtract(outAmount).add(inAmount);
                map.put("lastamount", lastamount);

                // 计算执行率=执行金额/最终预算数
                if (debitamount.containsKey(pk_inoutbusiclass + zjlyname)) {
                    BigDecimal localdebitamount = convertToBigDecimal(debitamount.get(pk_inoutbusiclass + zjlyname));// 执行金额
                    map.put("localdebitamount", localdebitamount);
                    map.put("jflx", zjlyname);
                    if (map.get("lastamount")!=null && BigDecimal.ZERO.compareTo(convertToBigDecimal(map.get("lastamount"))) != 0) {
                        if (localdebitamount==null || BigDecimal.ZERO.compareTo(localdebitamount) == 0) {
                            map.put("zxl", BigDecimal.ZERO);
                        } else {
                            map.put("zxl", localdebitamount.divide(convertToBigDecimal(map.get("lastamount")), 8, BigDecimal.ROUND_HALF_UP));
                        }
                    }
                }
            }
        }

        return mainData;
    }

    /**
     * 通用类型转换工具：将任意数字类型转为BigDecimal（支持null）
     * @param value 输入值（可是int/double/long/BigDecimal/String数字）
     * @return 转换后的BigDecimal，无效值返回null
     */
    private BigDecimal convertToBigDecimal(Object value) {
        if (value == null) {
            return null;
        }

        try {
            if (value instanceof BigDecimal) {
                return (BigDecimal) value;
            } else if (value instanceof Integer) {
                return BigDecimal.valueOf((Integer) value);
            } else if (value instanceof Long) {
                return BigDecimal.valueOf((Long) value);
            } else if (value instanceof Double) {
                // 注意：double可能有精度损失，建议优先使用BigDecimal或String存储金额
                return BigDecimal.valueOf((Double) value);
            } else if (value instanceof String) {
                // 支持字符串格式的数字（如"123.45"）
                return new BigDecimal((String) value);
            } else {
                // 不支持的类型
                return null;
            }
        } catch (NumberFormatException e) {
            // 字符串无法转为数字时返回null
            return null;
        }
    }


    /**
     * 初始化metadata
     *
     * @return
     */
    private MetaData initMetaData() {
        MetaData metaData = new MetaData();
        List<String> numberColumnList = Arrays.asList(numberCloumns);
        for (int i = 0; i < fields.length; i++) {
            Field field = new Field();
            field.setFldname(fields[i]);
            field.setCaption(fieldCaptions[i]);
            if (numberColumnList.contains(fields[i])) {
                field.setDbColumnType(Types.DECIMAL);
                field.setPrecision(40);
                field.setScale(2);
            } else {
                field.setDbColumnType(Types.VARCHAR);
            }
            metaData.addField(field);
        }
        return metaData;
    }

    public static DataSet buildDataSetobj(MetaData meta, List<Map<String, Object>> list, String[] fields) {
        if (ArrayUtils.isEmpty(fields)) {
            ExceptionUtils.wrappBusinessException("报表字段列不能为空，请检查！！！");
        }
        DataSet ds = null;
        if (null == list || list.size() == 0) {
            // 返回空数据集, 用于给语义模型加载空数据用
            Object[][] datas = new Object[1][fields.length];
            ds = new DataSet(meta, datas);
        } else {
            Object[][] datas = new Object[list.size()][fields.length];
            for (int i = 0; i < list.size(); i++) {
                for (int j = 0; j < fields.length; j++) {
                    datas[i][j] = list.get(i).get(fields[j]);
                }
            }
            ds = new DataSet(meta, datas);
        }
        return ds;
    }
}